package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import modelo.Responsable;
import util.ConexionBD;
import excepciones.*;


public class ResponsableDAO extends BaseDAO {

	public Responsable validarLogin(String correo, String password) {
		String query = "select nombres, correo, password "
				+ " from responsable where correo = ? and password = ?";
		Responsable responsable = new Responsable();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, correo);
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if (rs.next()) {
				responsable.setNombres(rs.getString("nombres"));
				responsable.setCorreo(rs.getString("correo"));
				responsable.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return responsable;
	}

	public Responsable ingresarResponsable(Responsable objResponsable) throws DAOExcepcion
	{
		
		String query =  "INSERT INTO responsable (nombres,paterno,materno,sexo,ndocumento,correo,password,celular,direccion,estado,tipo,idTipodocumento) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, objResponsable.getNombres());
			stmt.setString(2, objResponsable.getPaterno());
			stmt.setString(3, objResponsable.getMaterno());
			stmt.setInt(4, objResponsable.getSexo());
			stmt.setString(5, objResponsable.getNroDocumento());
			stmt.setString(6, objResponsable.getCorreo());
			stmt.setString(7, objResponsable.getPassword());
			stmt.setString(8, objResponsable.getCelular());
			stmt.setString(9, objResponsable.getDireccion());
			stmt.setInt(10, objResponsable.getEstado());
			stmt.setInt(11, objResponsable.getTipo());
			stmt.setInt(12, objResponsable.getIdTipoDocumento());
			
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			objResponsable.setIdResponsable(id);
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return objResponsable;
	}

	public Collection<Responsable> listarResponsables(String opcion,String valor) throws DAOExcepcion {
		
		String query = "select idresponsable,nombres,paterno,materno,correo,ndocumento from responsable where 1=1 ";
		String multipleLike = "%";
		if (opcion.equals("todo"))
		{
		}else if(opcion.equals("nombre")){
			query  = query + " and nombres like ?";
		}else if (opcion.equals("correo")){
			query  = query +  " and correo  like ?";
		}else if (opcion.equals("ndocumento")){
			query  = query +  " and ndocumento = ?";
			multipleLike = "";
		}
		
		
		
		
		Collection<Responsable> c = new ArrayList<Responsable>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			if (!opcion.equals("todo"))
				stmt.setString(1, multipleLike + valor + multipleLike);

			rs = stmt.executeQuery();
			while (rs.next()) {
				Responsable re = new Responsable();
				re.setIdResponsable(rs.getInt("IdResponsable"));
				re.setNombres(rs.getString("nombres"));
				re.setPaterno(rs.getString("paterno"));
				re.setMaterno(rs.getString("materno"));
				re.setCorreo(rs.getString("correo"));
				re.setNroDocumento(rs.getString("ndocumento"));
				c.add(re);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

	public Responsable obtenerResponsable(int idResponsable) throws DAOExcepcion {
		Responsable res = new Responsable();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select idresponsable,nombres,paterno,materno,sexo,ndocumento,correo,password,celular,direccion,estado,tipo,idtipodocumento from Responsable where idresponsable=?";

			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idResponsable);
			rs = stmt.executeQuery();
			if (rs.next()) {
				res.setIdResponsable(rs.getInt(1));
				res.setNombres(rs.getString(2));
				res.setPaterno(rs.getString(3));
				res.setMaterno(rs.getString(4));
				res.setSexo(rs.getInt(5));
				res.setNroDocumento(rs.getString(6));
				res.setCorreo(rs.getString(7));
				res.setPassword(rs.getString(8));
				res.setCelular(rs.getString(9));
				res.setDireccion(rs.getString(10));
				res.setEstado(rs.getInt(11));
				res.setTipo(rs.getInt(12));
				res.setIdTipoDocumento(rs.getInt(13));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return res;
	}
	
	public Responsable actualizarResponsable(Responsable objResponsable) throws DAOExcepcion {
		String query = "update responsable set nombres=?,paterno=?,materno=?,sexo=?,ndocumento=?,correo=?,password=?,celular=?,direccion=?,tipo=?,idtipodocumento=? where idresponsable=?";

		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setString(1, objResponsable.getNombres());
			stmt.setString(2, objResponsable.getPaterno());
			stmt.setString(3, objResponsable.getMaterno());
			stmt.setInt(4, objResponsable.getSexo());
			stmt.setString(5, objResponsable.getNroDocumento());
			stmt.setString(6, objResponsable.getCorreo());
			stmt.setString(7, objResponsable.getPassword());
			stmt.setString(8, objResponsable.getCelular());
			stmt.setString(9, objResponsable.getDireccion());
			stmt.setInt(10, objResponsable.getTipo());
			stmt.setInt(11, objResponsable.getIdTipoDocumento());
			stmt.setInt(12, objResponsable.getIdResponsable());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return objResponsable;
	}

	public void eliminarResponsable(int idResponsable) throws DAOExcepcion {
		String query = "delete from Responsable WHERE idresponsable =?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idResponsable);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
}
